﻿
CREATE proc dbo.SavePlzOrt
	@EPLZ int = null, 
	@LKZ char(3), 
	@PLZ nchar(10), 
	@Ort nvarchar(100), 
	@Zone smallint = null
as
begin
	set nocount on;
	declare @errorMessage nvarchar(400),
			@SetTran bit = 0;

	if @LKZ is null
	begin
		set @errorMessage = 'LKZ is null';

		goto ExitOnError
	end

	if @PLZ is null
	begin
		set @errorMessage = 'PLZ is null';

		goto ExitOnError
	end

	if @Ort is null
	begin
		set @errorMessage = 'Ort is null';

		goto ExitOnError
	end

	if @EPLZ is null
	begin
		select	@EPLZ = max(p.EPLZ) + 1
		from	dbo.Postleitzahl p

		
	end

	if exists(select 1 from dbo.Postleitzahl p where p.plz = @PLZ and p.Ort = @Ort)
	begin
		select	@EPLZ = p.EPLZ
		from	dbo.Postleitzahl p
		where	p.plz = @PLZ and p.Ort = @Ort
	end

	if @EPLZ is null
	begin
		set @errorMessage = 'EPLZ is null';

		goto ExitOnError
	end

	if xact_state() = 0
	begin
		set @SetTran = 1
		begin tran
	end

	begin try

		;with data
		as
		(
			select	@EPLZ 'EPLZ',
					@LKZ 'LKZ',
					@PLZ 'PLZ',
					@Ort 'Ort',
					@Zone 'Zone'
		)
		merge into dbo.Postleitzahl t
		using data s on
			s.EPLZ = t.EPLZ
		when matched then
			update
				set LKZ		= s.LKZ, 
					PLZ		= s.PLZ, 
					Ort		= s.Ort, 
					Zone	= s.Zone
		when not matched then
			insert (EPLZ, LKZ, PLZ, Ort, Zone)
			values (
					s.EPLZ,
					s.LKZ, 
					s.PLZ, 
					s.Ort, 
					s.Zone
			);

		if coalesce(@EPLZ, 0) = 0
		begin
			select @EPLZ = SCOPE_IDENTITY();
		end

		if @SetTran = 1
		begin
			commit tran
		end
    
		select  @EPLZ 'EPLZ';

	end try
	begin catch
		set @errorMessage = ERROR_MESSAGE();
		goto ExitOnError
	end catch

	return 0;

	ExitOnError:
        if  @SetTran = 1
        begin
            rollback tran
        end
        
        raiserror(@ErrorMessage, 16, 2)
        
        return 1
end